<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="coinfaces">

        <title>从记账app的bug中恢复</title>

    <link rel="stylesheet" href="../../theme/css/style.css">
    <link rel="stylesheet" href="../../theme/css/pygments.css">



</head>

<body>
    <div class="content">
<h1>从记账app的bug中恢复</h1>    <p>
        under
            <a href="../../tags/android/">android</a>
            <a href="../../tags/fix/">fix</a>
    </p>
    <p>
        in <a href="../../categories/tech/">tech</a>
    </p>
    <p>Published: 2016-05-02</p>

        <p>Last edit: 2016-08-10</p>

    <p>最近在一次重命名<a class="reference external" href="https://github.com/codinguser/gnucash-android">gnucash-android</a>账户后，我弄丢了所有被重命名账户的交易信息（但在另一台安卓设备上没有出现这个bug），幸运的是，记录现金花费的账户没有受到影响，而且更幸运的是，一周前刚备份了该app的数据库文件。</p>
<p>该app将大部分信息存储在三张表中，账户信息存储在“accounts”里，交易信息存储在“transaction”里，但与交易双方账户的uid和金额被存储在“splits”里。检查了损坏的数据库文件后，发现在“splits”里所有属于被重命名的账户的记录都丢失了，但有关的交易信息还在“transactions”里保存着。</p>
<p>下面的脚本可以列出备份后的所有交易（它不会列出被修改的交易，但对于这次恢复够用了），这样就不用担心遗漏任何交易，之后的恢复进展很顺利。</p>
<div class="highlight docutils container">
<pre class="code python literal-block">
<span class="ch">#!/usr/bin/env python2</span>

<span class="kn">import</span> <span class="nn">sqlite3</span>
<span class="kn">from</span> <span class="nn">datetime</span> <span class="kn">import</span> <span class="n">datetime</span>


<span class="k">def</span> <span class="nf">open_db</span><span class="p">(</span><span class="n">db_path</span><span class="p">):</span>
    <span class="k">return</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">db_path</span><span class="p">)</span>


<span class="k">def</span> <span class="nf">close_db</span><span class="p">(</span><span class="n">db</span><span class="p">):</span>
    <span class="k">pass</span>


<span class="k">def</span> <span class="nf">get_account_uids</span><span class="p">(</span><span class="n">db</span><span class="p">):</span>
    <span class="sd">'''Return uids of all the accounts in a db as a set.'''</span>

    <span class="n">account_iter</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT uid FROM accounts'</span><span class="p">)</span>

    <span class="k">return</span> <span class="nb">set</span><span class="p">(</span><span class="n">account_iter</span><span class="p">)</span>


<span class="k">def</span> <span class="nf">print_account_info</span><span class="p">(</span><span class="n">account_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">):</span>
    <span class="n">account_info</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT * FROM accounts WHERE uid=?'</span><span class="p">,</span> <span class="n">account_uid</span><span class="p">)</span>
    <span class="nb">print</span> <span class="nb">list</span><span class="p">(</span><span class="n">account_info</span><span class="p">)</span><span class="o">.</span><span class="n">encode</span><span class="p">(</span><span class="s1">'utf-8'</span><span class="p">)</span>


<span class="k">def</span> <span class="nf">get_account_name</span><span class="p">(</span><span class="n">account_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">):</span>
    <span class="n">name</span><span class="p">,</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT full_name FROM accounts WHERE uid=?'</span><span class="p">,</span> <span class="p">(</span><span class="n">account_uid</span><span class="p">,))</span><span class="o">.</span><span class="n">next</span><span class="p">()</span>
    <span class="k">return</span> <span class="n">name</span>


<span class="k">def</span> <span class="nf">print_account_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">):</span>
    <span class="n">old_uids</span> <span class="o">=</span> <span class="n">get_account_uids</span><span class="p">(</span><span class="n">old_db</span><span class="p">)</span>
    <span class="n">new_uids</span> <span class="o">=</span> <span class="n">get_account_uids</span><span class="p">(</span><span class="n">new_db</span><span class="p">)</span>
    <span class="n">changed_uids</span> <span class="o">=</span> <span class="nb">set</span><span class="p">(</span><span class="n">new_uids</span><span class="p">)</span><span class="o">.</span><span class="n">difference</span><span class="p">(</span><span class="nb">set</span><span class="p">(</span><span class="n">old_uids</span><span class="p">))</span>
    <span class="k">for</span> <span class="n">uid</span> <span class="ow">in</span> <span class="n">changed_uids</span><span class="p">:</span>
        <span class="n">print_account_info</span><span class="p">(</span><span class="n">uid</span><span class="p">,</span> <span class="n">new_db</span><span class="p">)</span>


<span class="k">def</span> <span class="nf">get_transaction_uids</span><span class="p">(</span><span class="n">db</span><span class="p">):</span>
    <span class="k">return</span> <span class="nb">set</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT uid FROM transactions'</span><span class="p">))</span>


<span class="k">def</span> <span class="nf">get_transaction_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">):</span>
    <span class="k">return</span> <span class="n">get_transaction_uids</span><span class="p">(</span><span class="n">new_db</span><span class="p">)</span><span class="o">.</span><span class="n">difference</span><span class="p">(</span><span class="n">get_transaction_uids</span><span class="p">(</span><span class="n">old_db</span><span class="p">))</span>


<span class="k">def</span> <span class="nf">get_transfer_info</span><span class="p">(</span><span class="n">transaction_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">):</span>
    <span class="n">splits</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT type,account_uid,amount FROM splits WHERE transaction_uid=?'</span><span class="p">,</span> <span class="n">transaction_uid</span><span class="p">))</span>
    <span class="n">split_count</span> <span class="o">=</span> <span class="nb">len</span><span class="p">(</span><span class="n">splits</span><span class="p">)</span>
    <span class="n">account_from</span><span class="p">,</span> <span class="n">account_to</span><span class="p">,</span> <span class="n">amount</span> <span class="o">=</span> <span class="s1">''</span><span class="p">,</span> <span class="s1">''</span><span class="p">,</span> <span class="s1">''</span>
    <span class="k">for</span> <span class="n">split_info</span> <span class="ow">in</span> <span class="n">splits</span><span class="p">:</span>
        <span class="n">transaction_type</span><span class="p">,</span> <span class="n">account_uid</span><span class="p">,</span> <span class="n">amount</span> <span class="o">=</span> <span class="n">split_info</span>
        <span class="k">if</span> <span class="n">transaction_type</span> <span class="o">==</span> <span class="s1">'DEBIT'</span><span class="p">:</span>
            <span class="n">account_to</span> <span class="o">=</span> <span class="n">get_account_name</span><span class="p">(</span><span class="n">account_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">)</span>
        <span class="k">if</span> <span class="n">transaction_type</span> <span class="o">==</span> <span class="s1">'CREDIT'</span><span class="p">:</span>
            <span class="n">account_from</span> <span class="o">=</span> <span class="n">get_account_name</span><span class="p">(</span><span class="n">account_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">)</span>
    <span class="k">return</span> <span class="n">amount</span><span class="p">,</span> <span class="n">account_from</span><span class="p">,</span> <span class="n">account_to</span>


<span class="k">def</span> <span class="nf">get_transaction_info</span><span class="p">(</span><span class="n">transaction_uid</span><span class="p">,</span> <span class="n">db</span><span class="p">):</span>
    <span class="n">name</span><span class="p">,</span> <span class="n">description</span><span class="p">,</span> <span class="n">timestamp</span> <span class="o">=</span> <span class="nb">next</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s1">'SELECT name,description,timestamp FROM transactions WHERE uid=?'</span><span class="p">,</span> <span class="n">transaction_uid</span><span class="p">))</span>
    <span class="n">time_text</span> <span class="o">=</span> <span class="n">timestamp_to_text</span><span class="p">(</span><span class="n">timestamp</span><span class="p">)</span>
    <span class="n">amount</span><span class="p">,</span> <span class="n">account_from</span><span class="p">,</span> <span class="n">account_to</span> <span class="o">=</span> <span class="n">get_transfer_info</span><span class="p">(</span><span class="n">transaction_uid</span><span class="p">,</span> <span class="n">new_db</span><span class="p">)</span>
    <span class="k">return</span> <span class="n">time_text</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">description</span><span class="p">,</span> <span class="n">account_from</span><span class="p">,</span> <span class="n">amount</span><span class="p">,</span> <span class="n">account_to</span>


<span class="k">def</span> <span class="nf">timestamp_to_text</span><span class="p">(</span><span class="n">timestamp</span><span class="p">):</span>
    <span class="n">seconds</span> <span class="o">=</span> <span class="nb">int</span><span class="p">(</span><span class="n">timestamp</span><span class="p">)</span> <span class="o">/</span> <span class="mf">1000.0</span>
    <span class="k">return</span> <span class="n">datetime</span><span class="o">.</span><span class="n">fromtimestamp</span><span class="p">(</span><span class="n">seconds</span><span class="p">)</span><span class="o">.</span><span class="n">strftime</span><span class="p">(</span><span class="s1">'%Y-%m-</span><span class="si">%d</span><span class="s1"> %H:%M'</span><span class="p">)</span>


<span class="k">def</span> <span class="nf">print_transaction_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">):</span>
    <span class="n">changed_transaction_uids</span> <span class="o">=</span> <span class="n">get_transaction_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">)</span>
    <span class="n">transaction_records</span> <span class="o">=</span> <span class="p">[]</span>
    <span class="k">for</span> <span class="n">transaction_uid</span> <span class="ow">in</span> <span class="n">changed_transaction_uids</span><span class="p">:</span>
        <span class="n">transaction_records</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">get_transaction_info</span><span class="p">(</span><span class="n">transaction_uid</span><span class="p">,</span> <span class="n">new_db</span><span class="p">))</span>

    <span class="k">for</span> <span class="n">transaction_info</span> <span class="ow">in</span> <span class="nb">sorted</span><span class="p">(</span><span class="n">transaction_records</span><span class="p">):</span>
        <span class="nb">print</span> <span class="p">(</span><span class="s1">'AT </span><span class="si">%s</span><span class="s1">: </span><span class="si">%s</span><span class="s1"> (</span><span class="si">%s</span><span class="s1">) </span><span class="si">%s</span><span class="s1"> =&gt; </span><span class="si">%s</span><span class="s1"> =&gt; </span><span class="si">%s</span><span class="s1">'</span> <span class="o">%</span> <span class="n">transaction_info</span><span class="p">)</span><span class="o">.</span><span class="n">encode</span><span class="p">(</span><span class="s1">'utf-8'</span><span class="p">)</span>


<span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s1">'__main__'</span><span class="p">:</span>
    <span class="kn">import</span> <span class="nn">sys</span>

    <span class="n">old_db_path</span><span class="p">,</span> <span class="n">new_db_path</span> <span class="o">=</span> <span class="n">sys</span><span class="o">.</span><span class="n">argv</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="n">sys</span><span class="o">.</span><span class="n">argv</span><span class="p">[</span><span class="mi">2</span><span class="p">]</span>

    <span class="n">old_db</span> <span class="o">=</span> <span class="n">open_db</span><span class="p">(</span><span class="n">old_db_path</span><span class="p">)</span>
    <span class="n">new_db</span> <span class="o">=</span> <span class="n">open_db</span><span class="p">(</span><span class="n">new_db_path</span><span class="p">)</span>

    <span class="n">print_account_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">)</span>
    <span class="n">print_transaction_changes</span><span class="p">(</span><span class="n">old_db</span><span class="p">,</span> <span class="n">new_db</span><span class="p">)</span>

    <span class="n">close_db</span><span class="p">(</span><span class="n">old_db</span><span class="p">)</span>
    <span class="n">close_db</span><span class="p">(</span><span class="n">new_db</span><span class="p">)</span>
</pre>
</div>
<p>编辑记录：</p>
<ul class="simple">
<li>2016-08-08编辑，嵌入gnucash_db_diff.py的内容</li>
<li>2016-08-10编辑，将编辑记录移到文章末尾</li>
</ul>
<p>（完）</p>

    </div>
<nav class="nav sidebar">
    <div class="siteinfo">
        <div class="sitename"><a href="../..">coinfaces</a></div>
        <div class="tagline">Another Side of Life</div>
    </div>

        <a href="../../categories/">categories</a>
        <a href="../..">&larr; Home</a>
</nav><footer class="footer">
    <span>
        &copy; jpli &middot;
        under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC-BY-SA 4.0</a>
    </span>
    |
    <span>
        Powered by:
        <a href="http://blog.getpelican.com/">Pelican</a>
    </span>
</footer></body>
</html>